﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SQLite;

namespace estiglp1112_5946_csharp
{
    class SQLHandler
    {
        private SQLiteConnection conn;

        /*esta class é responsavel pela ligacao á base de dados, permite introducao na base de dados e ler todos os dados na base de dados*/

        public SQLHandler(string database)
        {
            this.connect(database);
        }

        //Cria a ligacao á base de dados
        private void connect(string database)
        {
            this.conn = new SQLiteConnection(@"Data Source=" + database + ";FailIfMissing=False;");
        }

        /// <summary>
        /// Cria a tabela na base de dados
        /// </summary>
        /// <param name="table">Nome da tabela</param>
        /// <param name="list">Lista de nomes de campos</param>
        public void createTable(string table)
        {
            this.conn.Open();
            var dbTrans = conn.BeginTransaction();
            SQLiteCommand cmd = conn.CreateCommand(); 
            cmd.CommandText = String.Format(@"CREATE TABLE if not exists ""{0}""(cod, name, degree, curse, catgory, regime, year, type_establishment, establishment);",table);
            cmd.ExecuteNonQuery();
            dbTrans.Commit();
            this.conn.Close();         
        }

        /// <summary>
        /// Insere dados na base de dados
        /// </summary>
        /// <param name="table">Nome da tabela</param>
        /// <param name="plist"> Lisyta de dados a ser inseridos na base de dados</param>
        public void insertData(string table, List<Teacher> plist)
        {
            this.conn.Open();
            var dbTrans = conn.BeginTransaction();
            SQLiteCommand cmd = conn.CreateCommand();         
            foreach (Teacher teacher in plist)
            {
                cmd.CommandText = String.Format("INSERT INTO \"{0}\"values({1});",table,teacher.ToString());
                cmd.ExecuteNonQuery();
            }
            dbTrans.Commit();
            this.conn.Close();

        }
        /// <summary>
        /// Devolve todos os registos na base de dados
        /// </summary>
        /// <param name="table">Nome da tabela</param>
        /// <returns>Lista de registos</returns>
        /// 
        public List<Teacher> getData(string table)
        {
            List<Teacher> all = new List<Teacher>();
            try
            {
                this.conn.Open();
                string sql = String.Format(@"SELECT * from ""{0}"";", table);
                SQLiteCommand cmd = new SQLiteCommand(sql);
                cmd.Connection = this.conn;
                SQLiteDataReader reg = cmd.ExecuteReader();                
                while (reg.Read())
                {
                    all.Add(new Teacher(reg.GetString(0),
                                         reg.GetString(1),
                                         reg.GetString(2),
                                         reg.GetString(3),
                                         reg.GetString(4),
                                         reg.GetString(5),
                                         reg.GetString(6),
                                         reg.GetString(7),
                                         reg.GetString(8)));
                }
                reg.Close();
                this.conn.Close();     
           
            }
            catch { MessageBox.Show("Error opening data table!"); }
            return all;
        }

        public void closeConn() 
        {
            this.conn.Close();
        }
    }
}
